<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Teams_db extends CI_Model
{
	function getTeams($data=false, $id=false)
	{
		if($data)
			$this->db->where($data);
		if($id)
			$this->db->where('id != ',$id);
		$query = $this->db->get('groups');
		return $query->result_array();
	}
	
	function save($data, $item=false)
	{	
		if (!$item)
		{			
			$this->db->trans_start();
			$this->db->insert('groups', $data);
			$item = $this->db->insert_id();
			$this->db->trans_complete(); 					
		}
		else
		{
			$this->db->trans_start();
			$this->db->where('group_id', $item);
			$this->db->update('groups', $data);		
			$this->db->trans_complete();
		}
		return $item;	
	}
	
	function delete($id)
	{
		if(count($id)>1)
			$this->db->where_in('group_id',$id);
		else
			$this->db->where('group_id',$id[0]);

		$this->db->delete('groups'); 
		return $id;
	}
	
	function getList()
	{		
		$sortname = $this->input->post('sortname');
		$sortorder = $this->input->post('sortorder');
		$page = $this->input->post('page');
		$rp = $this->input->post('rp');		
		
		//$query = $this->input->post('query');
		//$qtype = $this->input->post('qtype');
		$squery = $this->input->post('squery');
		
		$cond = "";

		if (!$sortname) $sortname = 'group_name';
		if (!$sortorder) $sortorder = 'ASC';
		
		
		if (!$page) $page = 1;
		if (!$rp) $rp = 40;
		
		$start = (($page-1) * $rp);
		
		$this->db->start_cache();
		if ($squery) $this->db->where("(LOWER(a.group_name) LIKE '%$squery%' OR ((SELECT LOWER(group_name) FROM groups where group_id = a.parent_group_id)) LIKE '%$squery%')");
		$this->db->select("a.group_id as pkey, a.group_id as test, a.group_name, (SELECT group_name FROM groups where group_id = a.parent_group_id) as parent_name, (SELECT COUNT(employee_group_id) FROM employee_groups where group_id = a.group_id) as members_count, (SELECT COUNT(group_approver_id) FROM group_approvers where group_id = a.group_id) as approvers_count",false);
		$this->db->order_by($sortname,$sortorder);
		$this->db->limit($rp, $start);		
		$query = $this->db->get("groups a");
		//$num = $this->db->count_all_results();
		$num = count($query->result_array());

		$this->db->flush_cache();
		
		
		$rows = array();
		if (isset($query))
		{			 
			foreach($query->result_array() as $row):			
				$key = key($row);
				$id = $row[$key];
				unset($row[$key]);
				$cell = array();
				foreach ($row as $item) { $cell[] = $item; }
				$rows[] = array( "id" => $id, "cell" => $cell );				
			endforeach; 
		}
		
		$data['rows'] = $rows;
		$data['page'] = $page;
		$data['total'] = $num;
		return $data;			
		
	}
	
	//Employee Groups
	
	function getTeamEmployees($data=false)
	{
		if($data)
			$this->db->where($data);
		$this->db->join('users b','b.id = a.user_id', 'left');
		$this->db->join('groups c','c.group_id = a.group_id', 'left');
		$query = $this->db->get('employee_groups a');
		return $query->result_array();
	}
	
	function getTeamEmployeesID($data=false, $ids=false)
	{
		if($data)
			$this->db->where($data);
		if($ids)
			$this->db->where_in('user_id', $ids);
		$this->db->select('user_id');
		$query = $this->db->get('employee_groups a');
		return $query->result_array();
	}
	
	function saveTeamEmployee($data, $item=false)
	{	
		if (!$item)
		{			
			$this->db->trans_start();
			$this->db->insert_batch('employee_groups', $data);
			$item = $this->db->insert_id();
			$this->db->trans_complete();				
		}
		else
		{
			$this->db->trans_start();
			$this->db->where('employee_group_id', $item);
			$this->db->update('employee_groups', $data);		
			$this->db->trans_complete();
		}
		return $item;
	}
	
	function deleteTeamEmployee($id)
	{
		if(count($id)>1)
			$this->db->where_in('employee_group_id',$id);
		else
			$this->db->where('employee_group_id',$id[0]);

		$this->db->delete('employee_groups'); 
		return $id;
	}
	
	function getListTeamEmployee($data=false)
	{		
		$sortname = $this->input->post('sortname');
		$sortorder = $this->input->post('sortorder');
		$page = $this->input->post('page');
		$rp = $this->input->post('rp');		
		
		//$query = $this->input->post('query');
		//$qtype = $this->input->post('qtype');
		$squery = $this->input->post('squery');
		
		$cond = "";
		
		if (!$page) $page = 1;
		if (!$rp) $rp = 40;
		
		$start = (($page-1) * $rp);
		
		$this->db->start_cache();
		if ($squery) $this->db->where("(LOWER(b.fullname) LIKE '%$squery%' OR LOWER(b.employee_no) LIKE '%$squery%' OR LOWER(b.username) LIKE '%$squery%')");
		$this->db->where($data);
		$this->db->select("a.employee_group_id as pkey, a.employee_group_id as test, b.employee_no, b.fullname, b.username, b.email, a.is_manager");
		$this->db->join('users b','b.id = a.user_id', 'left');
		$this->db->order_by('a.is_manager','DESC');
		$this->db->order_by('b.fullname','ASC');
		$this->db->limit($rp, $start);		
		$query = $this->db->get("employee_groups a");
		$num = count($query->result_array());

		$this->db->flush_cache();
		
		
		$rows = array();
		if (isset($query))
		{			 
			foreach($query->result_array() as $row):			
				$key = key($row);
				$id = $row[$key];
				unset($row[$key]);
				$cell = array();
				foreach ($row as $item) { $cell[] = $item; }
				$rows[] = array( "id" => $id, "cell" => $cell );				
			endforeach; 
		}
		
		$data['rows'] = $rows;
		$data['page'] = $page;
		$data['total'] = $num;
		return $data;			
		
	}
	
	//Approver Groups
	
	function getTeamApprovers($data=false)
	{
		if($data)
			$this->db->where($data);
		$this->db->join('users b','b.id = a.user_id', 'left');
		$this->db->join('groups c','c.group_id = a.group_id', 'left');
		$query = $this->db->get('group_approvers a');
		return $query->result_array();
	}
	
	function getTeamApproversID($data=false, $ids=false)
	{
		if($data)
			$this->db->where($data);
		if($ids)
			$this->db->where_in('user_id', $ids);
		$this->db->select('user_id');
		$query = $this->db->get('group_approvers a');
		return $query->result_array();
	}
	
	function saveTeamApprover($data, $item=false)
	{	
		if (!$item)
		{			
			$this->db->trans_start();
			$this->db->insert_batch('group_approvers', $data);
			$item = $this->db->insert_id();
			$this->db->trans_complete();				
		}
		else
		{
			$this->db->trans_start();
			$this->db->where('group_approver_id', $item);
			$this->db->update('group_approvers', $data);		
			$this->db->trans_complete();
		}
		return $item;
	}
	
	function deleteTeamApprover($id)
	{
		if(count($id)>1)
			$this->db->where_in('group_approver_id',$id);
		else
			$this->db->where('group_approver_id',$id[0]);

		$this->db->delete('group_approvers'); 
		return $id;
	}
	
	function getListTeamApprover($data=false)
	{		
		$sortname = $this->input->post('sortname');
		$sortorder = $this->input->post('sortorder');
		$page = $this->input->post('page');
		$rp = $this->input->post('rp');		
		
		//$query = $this->input->post('query');
		//$qtype = $this->input->post('qtype');
		$squery = $this->input->post('squery');
		
		$cond = "";
		
		if (!$page) $page = 1;
		if (!$rp) $rp = 40;
		
		$start = (($page-1) * $rp);
		
		$this->db->start_cache();
		if ($squery) $this->db->where("(LOWER(b.fullname) LIKE '%$squery%' OR LOWER(b.employee_no) LIKE '%$squery%' OR LOWER(b.username) LIKE '%$squery%')");
		$this->db->where($data);
		$this->db->select("a.group_approver_id as pkey, a.group_approver_id as test, b.employee_no, b.fullname, b.username, b.email");
		$this->db->join('users b','b.id = a.user_id', 'left');
		$this->db->order_by('b.fullname','ASC');
		$this->db->limit($rp, $start);		
		$query = $this->db->get("group_approvers a");
		$num = count($query->result_array());

		$this->db->flush_cache();
		
		
		$rows = array();
		if (isset($query))
		{			 
			foreach($query->result_array() as $row):			
				$key = key($row);
				$id = $row[$key];
				unset($row[$key]);
				$cell = array();
				foreach ($row as $item) { $cell[] = $item; }
				$rows[] = array( "id" => $id, "cell" => $cell );				
			endforeach; 
		}
		
		$data['rows'] = $rows;
		$data['page'] = $page;
		$data['total'] = $num;
		return $data;			
		
	}

}

?>